﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Resources;
using System.Diagnostics;
using System.IO;
using System.Windows;

namespace Manage_Garage_Car.DataAccessLayer
{
    class ConnectData   
    {
        #region Fields

        private SqlConnection m_connection;

        private DataTable m_dTable;

        private SqlCommand m_command;

        private SqlDataAdapter m_dAdapter;

        private DataSet m_dSet;

        #endregion

        #region Properties

        public DataTable GetDataTable(string _strSql, SqlParameter[] _sqlParam = null)
        {
            try
            {
                m_command.CommandText = _strSql;
                m_command.Connection = m_connection;
                m_command.CommandType = CommandType.StoredProcedure;
                    
                if (_sqlParam != null)
                {
                    for (int i = 0; i < _sqlParam.Length; i++)
                    {
                        m_command.Parameters.Add(_sqlParam[i]);
                    }
                }

                m_dAdapter.SelectCommand = m_command;
                m_dTable.Clear();
                m_dAdapter.Fill(m_dTable);
            }
            catch (SqlException sqlEx)
            {
                MessageBox.Show("Lỗi: " + sqlEx.Message);
            }

            return m_dTable;
        }

        public DataSet GetDataSet(string _strSql, SqlParameter[] _sqlParam = null)
        {
                try
                {
                    m_command.CommandText = _strSql;
                    m_command.Connection = m_connection;
                    m_command.CommandType = CommandType.StoredProcedure;

                    if (_sqlParam != null)
                    {
                        for (int i = 0; i < _sqlParam.Length; i++)
                        {
                            m_command.Parameters.Add(_sqlParam[i]);
                        }
                    }

                    m_dAdapter.SelectCommand = m_command;
                    m_dAdapter.Fill(m_dSet);
                }
                catch (SqlException sqlEx)
                {
                    MessageBox.Show("Lỗi: " + sqlEx.Message);
                }

                return m_dSet;
        }

        public SqlParameter[] GetSqlParam(string _strSql, SqlParameter[] _sqlParam = null)
        {
            try
            {
                m_command.CommandText = _strSql;
                m_command.Connection = m_connection;
                m_command.CommandType = CommandType.StoredProcedure;

                if (_sqlParam != null)
                {
                    for (int i = 0; i < _sqlParam.Length; i++)
                    {
                        m_command.Parameters.Add(_sqlParam[i]);
                    }
                }

                m_connection.Open();
                m_command.ExecuteReader();
            }
            catch (Exception Ex)
            {
                MessageBox.Show("Lỗi: " + Ex.Message);
            }
            finally
            {
                m_command.Parameters.Clear();
                m_connection.Close();
            }
            return _sqlParam;
        }

        public void SetDataTable(string _strSql, SqlParameter[] _sqlParam = null)
        {
            try
            {
                m_command.CommandText = _strSql;
                m_command.Connection = m_connection;
                m_command.CommandType = CommandType.StoredProcedure;

                if (_sqlParam != null)
                {
                    for (int i = 0; i < _sqlParam.Length; i++)
                    {
                        m_command.Parameters.Add(_sqlParam[i]);
                    }
                }

                m_connection.Open();
                m_command.ExecuteReader();
            }
            catch (Exception Ex)
            {
                MessageBox.Show("Lỗi: " + Ex.Message);
            }
            finally
            {
                m_command.Parameters.Clear();
                m_connection.Close();
            }
        }

        public void SetDataTable1(string _strSql)
        {
            try
            {
                m_command.CommandText = _strSql;
                m_command.Connection = m_connection;
                m_command.CommandType = CommandType.StoredProcedure;

                m_command.Parameters.Add("@BienSo", SqlDbType.VarChar, 20).Value = "asas";
                m_command.Parameters.Add("@TenKhachHang", SqlDbType.NVarChar, 100).Value = "asadd";
                m_command.Parameters.Add("@MaHX", SqlDbType.Char, 10).Value = "HX1";
                m_command.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 50).Value = "asadsa";
                m_command.Parameters.Add("@DienThoai", SqlDbType.VarChar, 20).Value = "021431";
                m_command.Parameters.Add("@Email", SqlDbType.VarChar, 100).Value = "03030";
                m_command.Parameters.Add("@TienNo", SqlDbType.Money).Value = 0;


                m_connection.Open();
                m_command.ExecuteReader();
                m_connection.Close();
                m_command.Parameters.Clear();
            }
            catch (Exception Ex)
            {
                MessageBox.Show("Lỗi 123: " + Ex.Message);
            }
        }

        #endregion

        #region Initialization

        public ConnectData()
        {
            m_connection = new SqlConnection();
            m_command = new SqlCommand();
            m_dAdapter = new SqlDataAdapter();
            m_dSet = new DataSet();
            m_dTable = new DataTable();

            Connect();
        }

        #endregion

        #region Connect

        public void Connect()
        {
            if (m_connection.State == ConnectionState.Closed)
            {
                string connectionString = "server=NKT_PC;database=QUANLYGARA;uid=sa;pwd=2281994";
                try
                {
                    m_connection = new SqlConnection(connectionString);
                    m_connection.Open();
                    m_connection.Close();
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("Error: " + ex.Message);
                }
                
            }
        }

        #endregion

        #region Update Data Source

        public int SaveAll()
        {
            int numRecord = 0;
            SqlTransaction sqlTransaction = null;

            try
            {
                if (m_connection.State == ConnectionState.Closed)
                {
                    m_connection.Open();
                }

                sqlTransaction = m_connection.BeginTransaction();

                m_dAdapter.SelectCommand.Transaction = sqlTransaction;

                SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(m_dAdapter);

                m_dAdapter.Update(m_dTable);

                sqlTransaction.Commit();
            }
            catch (System.Exception ex)
            {
                if (sqlTransaction != null)
                {
                    sqlTransaction.Rollback();
                }
                MessageBox.Show("Lỗi:" + ex.Message);
            }

            if (m_connection.State == ConnectionState.Open)
            {
                m_connection.Close();
            }

            return numRecord;
        }

        #endregion

        #region Get String Connection

        private string GetConnectionString()
        {
            string connectionString = null;

            Assembly assembly = Assembly.GetExecutingAssembly();
            Stream stream = assembly.GetManifestResourceStream("StringConnection.txt");
            StreamReader streamReader = new StreamReader(stream);
            TextReader textReader = new StreamReader(stream);

            if ((connectionString = textReader.ReadLine()) == null)
            {
                return null;
            }

            return connectionString;
        }

#endregion

    }
}
